import datetime
import json
import random

import pymysql
import requests
from snowflake import client

# mysql连接启动命令: snowflake_start_server --address=localhost --port=8910 --dc=1 --worker=1
connect = pymysql.connect(host='127.0.0.1', user='root', password='1905120210', port=3306, database='video-recommendation-system')
cursor = connect.cursor()
keyword='猫'
headers = {
    'Accept': '*/*',
    'Accept-Encoding': 'gzip, deflate, br, zstd',
    'Accept-Language': 'zh-CN,zh;q=0.9',
    'Origin': 'https://space.bilibili.com',
    'Priority': 'u=1, i',
    'Referer': 'https://space.bilibili.com/1528320969/video',
    'Sec-Ch-Ua': '"Google Chrome";v="125", "Chromium";v="125", "Not.A/Brand";v="24"',
    'Sec-Ch-Ua-Mobile': '?0',
    'Sec-Ch-Ua-Platform': '"Windows"',
    'Sec-Fetch-Dest': 'empty',
    'Sec-Fetch-Mode': 'cors',
    'Sec-Fetch-Site': 'same-site',
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
}
cookies = {
    "buvid3": "4801BEAA-3DFB-D9AE-5556-97F83D30491E64006infoc",
    "b_nut": "1736647564",
    "_uuid": "6BA4C24A-5B8D-E8B5-47F6-335B5529E7D664855infoc",
    "header_theme_version": "CLOSE",
    "enable_web_push": "DISABLE",
    "buvid4": "459C9636-EE60-0103-2661-43DEC490540564971-025011202-GpO0ooDfzNOGRZ2ChuLHuzJQeNeIt9K%2B90Gh2T8yliDjfmmtxlF28QXZk0roEX9c",
    "LIVE_BUVID": "AUTO2517366476515173",
    "DedeUserID": "1528320969",
    "DedeUserID__ckMd5": "a7afddbec068536a",
    "rpdid": "0zbfVG41Ze|RHvaVXWS|1MY|3w1TwXsx",
    "hit-dyn-v2": "1",
    "CURRENT_QUALITY": "80",
    "PVID": "2",
    "enable_feed_channel": "ENABLE",
    "fingerprint": "5d7ed884f8edd5f9ea3654793536d3e2",
    "buvid_fp_plain": "undefined",
    "buvid_fp": "5d7ed884f8edd5f9ea3654793536d3e2",
    "home_feed_column": "5",
    "browser_resolution": "1745-835",
    "bili_ticket": "eyJhbGciOiJIUzI1NiIsImtpZCI6InMwMyIsInR5cCI6IkpXVCJ9.eyJleHAiOjE3NDM2ODE1NDUsImlhdCI6MTc0MzQyMjI4NSwicGx0IjotMX0.vYDIaUqWMpITK3otljKy1PBKUt731-DCVglqlLq7fws",
    "bili_ticket_expires": "1743681485",
    "SESSDATA": "ff6b0981%2C1758974348%2C0d148%2A32CjCQdI8Ml_BeLQ7ColVsw9w53jonBxai4KNzwqj09-1LCqR7_QzCeAsxzvuZgupf5QoSVkZGN3o1b0xnU1JsQWExYV91WnQxRmdBTXdwRmpCbVJ4ZVg2REtiSWVJaHdrRlpEbW5qSy02ZnQzWW1LLUpuWWxKOGl6bXNISkdESTBvTkxfcE9kY0VBIIEC",
    "bili_jct": "937f59496ffed0a92ff6c40705cfc20d",
    "sid": "7hvv8ji0",
    "bp_t_offset_1528320969": "1050485284761763840",
    "CURRENT_FNVAL": "4048",
    "b_lsid": "EFC810379_195FB5CBD3B"
}
for i in range(0, 5):
    print('现在爬取到第' + str(i+1) + '页')
    try:
        url = 'https://api.bilibili.com/x/web-interface/wbi/search/type?category_id=&search_type=bili_user&ad_resource=5646&__refresh__=true&_extra=&context=&page=' + str(i+1) + '&page_size=50&order=&duration=&from_source=&from_spmid=333.337&platform=pc&highlight=1&single_column=0&keyword='+keyword+'&qv_id=vsuJLUbTKXXRPlCDZR8JoUcExmwfaYlU&source_tag=3&gaia_vtoken=&order_sort=0&user_type=0&dynamic_offset=24&web_location=1430654&w_rid=a30d79db1aaa527244797255635f1164&wts=1717985144'
        # 获取json文本信息
        get = json.loads(requests.get(url, headers=headers,cookies=cookies).text)
        arr = get["data"]['result']
        for o in arr:
            # tname:视频类别 pic:封面地址 desc:视频简介 '//player.bilibili.com/player.html?bvid='+bvid:视频资源地址 title:视频名称
            # 舍弃重复数据
            # resource = '//player.bilibili.com/player.html?bvid=' + o['bvid']
            u_name = o['uname']
            cursor.execute('select * from user where name = %s',(u_name))
            fetchall = cursor.fetchall()
            if not fetchall:
                u_account = str(client.get_guid())
                u_password = str(client.get_guid())
                # 生成随机手机号
                # 防止手机号重复
                u_phone_number = '1' + "".join(random.choices("3456789", k=1)) + "".join(random.choices("0123456789", k=9))
                cursor.execute('select * from user where phone_number = %s', u_phone_number)
                while cursor.fetchall():
                    u_phone_number = '1' + "".join(random.choices("3456789", k=1)) + "".join(random.choices("0123456789", k=9))
                u_introduction=o['usign']
                if u_introduction == '-' or u_introduction == '':
                    u_introduction = "这个用户什么都没说~"
                u_avatar_url=o['upic']
                u_create_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                u_deleted = 0
                u_deleted_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                # 插入user表
                try:
                    cursor.execute('insert into user(name,account,password,phone_number,introduction,avatar_url,create_time,deleted,deleted_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',( u_name, u_account, u_password, u_phone_number, u_introduction, u_avatar_url,u_create_time, u_deleted, u_deleted_time))
                    connect.commit()
                except Exception as e:
                    print('user插入出现问题：%s' % (e))
                    connect.rollback()
                # 查询此用户的视频信息
                u_id = cursor.lastrowid
                for b in o['res']:
                    v_title = b['title']
                    cursor.execute('select * from video where title = %s',v_title)
                    re = cursor.fetchall()
                    if not re:
                        v_introduction=b['desc']
                        if v_introduction == '-' or v_introduction == '':
                            v_introduction = v_title
                        v_cover = b['pic']
                        v_resource = '//player.bilibili.com/player.html?bvid='+b['bvid']
                        v_classification = json.loads(requests.get('https://api.bilibili.com/x/web-interface/wbi/view/detail?bvid='+b['bvid'],headers=headers,cookies=cookies).text)['data']['View']['tname']
                        v_publisher_id = u_id
                        v_create_time=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                        v_deleted = 0
                        v_deleted_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                        try:
                            cursor.execute('insert into video(title,introduction,cover,resource,classification,publisher_id,create_time,deleted,deleted_time) value (%s,%s,%s,%s,%s,%s,%s,%s,%s)',(v_title,v_introduction,v_cover,v_resource,v_classification,v_publisher_id,v_create_time,v_deleted,v_deleted_time))
                            connect.commit()
                        except Exception as e:
                            print('video插入出现问题：%s' % (e))
                            connect.rollback()

    except Exception as e2:
        print('数据获取时发生错误,错误信息如下: %s' % (e2))